package com.company.common.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import com.company.common.exception.BizException;
import com.company.user.entity.UserEntity;
import com.company.user.vo.ExcelStaffListVo;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Excel封装工具类
 */
@Slf4j
public class ImportExcelUtils {
    /**
     * excel员工数据处理
     */
    public static List<ExcelStaffListVo> importStaffUser(MultipartFile mFile) {
        String originalFilename = mFile.getOriginalFilename();
        if (StringUtils.isBlank(originalFilename)) {
            throw new BizException("", "文件选择异常");
        }
        //获取文件后缀
        String substring = originalFilename.substring(originalFilename.lastIndexOf("."));
        //创建储存所有的员工
        List<UserEntity> userEntities = new ArrayList<>();
        List<ExcelStaffListVo> excelStaffListVos = new ArrayList<>();
        List<Row> rowLIst = new ArrayList<>();
        File file = new File(mFile.getOriginalFilename());
        try {
            FileUtils.copyInputStreamToFile(mFile.getInputStream(), file);
            if (substring.equals(".xls")) {
                HSSFWorkbook sheets = new HSSFWorkbook(new FileInputStream(file.getPath()));
                HSSFSheet sheetAt = sheets.getSheetAt(0);
                for (Row row : sheetAt) {
                    rowLIst.add(row);
                }
                // 会在本地产生临时文件，用完后需要删除
                String path = file.getPath();
                if (file.exists()) {
                    boolean delete = file.delete();
                    if(delete){
                        log.info("文件"+path+"删除成功");
                    }else{
                        log.info("文件"+path+"删除失败");
                    }
                }
            } else if (substring.equals(".xlsx")) {
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(new FileInputStream(file.getPath()));
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
                for (Row row : xssfSheet) {
                    rowLIst.add(row);
                }

                // 会在本地产生临时文件，用完后需要删除
                String path = file.getPath();
                if (file.exists()) {
                    boolean delete = file.delete();
                    if(delete){
                        log.info("文件"+path+"删除成功");
                    }else{
                        log.info("文件"+path+"删除失败");
                    }
                }
            } else {
                String path = file.getPath();
                if (file.exists()) {
                    boolean delete = file.delete();
                    if(delete){
                        log.info("文件"+path+"删除成功");
                    }else{
                        log.info("文件"+path+"删除失败");
                    }
                }
                throw new BizException("", "文件类型不对请使用.xls .xlsx");
            }
        } catch (IOException e) {
            // 会在本地产生临时文件，用完后需要删除
            if (file.exists()) {
                boolean delete = file.delete();
            }
            log.info("异常:" + e.toString());
            e.printStackTrace();
        }
        if (rowLIst.size() <= 1) {
            throw new BizException("", "请勿上传空的模板");
        }
        //判断模板里面的字段是否正确
        compareExcel(rowLIst.get(0));
        for (Row row : rowLIst) {
            //去除第一行的目录
            if(row.getRowNum()==0){
                continue;
            }
            ExcelStaffListVo excelStaffListVo = new ExcelStaffListVo();
            excelStaffListVos.add(excelStaffListVo.copyTo(row));
        }
        return excelStaffListVos;
    }

    //判断execel 模板是否正确
    private static void compareExcel(Row row) {
        if (row.getPhysicalNumberOfCells() != 8) {
            throw new BizException("", "请使用最新的模板,或excel模板格式有误");
        }
        boolean f=("姓名".equals(row.getCell(0).toString())
                &&"账号".equals(row.getCell(1).toString())
                &&"密码".equals(row.getCell(2).toString())
                &&"部门".equals(row.getCell(3).toString())
                &&"职位".equals(row.getCell(4).toString())
                &&"手机号".equals(row.getCell(5).toString())
                &&"用户描述".equals(row.getCell(6).toString())
                &&"备注".equals(row.getCell(7).toString()));
        if (!f) {
            throw new BizException("", "请使用最新的模板,或excel模板格式有误");
        }
    }
}
